package authoritah.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

public class Member {
	private int id;
	private String name;
	private int initialPoints;
	private int status;

	// Member statuses
	public int NORMAL = 0;
	public int DORMANT = 1;
	public int TRIAL = 2;
	public int RETRIAL = 3;
	
	/////////////////////////
	// Getters and Setters //
	/////////////////////////
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getInitialPoints() {
		return initialPoints;
	}
	public void setInitialPoints(int initialPoints) {
		this.initialPoints = initialPoints;
	}
	public int getStatus() {
		return status;
	}
	public void setStatus(int status) {
		this.status = status;
	}
	
	//////////////////////
	// Database methods //
	//////////////////////
	
	/**
	 * Retrieves a member from the database file. If no member with supplied id exists, returns null.
	 * @param id
	 * @return
	 */
	public static Member getMember(int id){
		Member member = new Member();
		try {
            ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM members WHERE id=?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            
            // Only interested in first result as id is primary key should never have more than result anyway.
        	rs.next();            	
            member.setId(rs.getInt("id"));
            member.setName(rs.getString("name"));
            member.setInitialPoints(rs.getInt("initial_points"));
            member.setStatus(rs.getInt("status"));
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        return member;
	}
	
	/**
	 * Retrieves a member from the database file. If no member with supplied name exists, returns null.
	 * @param name
	 * @return
	 */
	public static Member getMember(String name){
		Member member = new Member();
		try {
            ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM members WHERE name=?");
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            
            // Only interested in first result as id is primary key should never have more than result anyway.
        	rs.next();            	
            member.setId(rs.getInt("id"));
            member.setName(rs.getString("name"));
            member.setInitialPoints(rs.getInt("initial_points"));
            member.setStatus(rs.getInt("status"));
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return member;
	}
	
	/**
	 * Stores a member into the database.
	 * @param member
	 */
	public static void createMember(Member member){
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("INSERT INTO members (name, initial_points, status) VALUES (?, ?, ?)");
            ps.setString(1, member.getName());
            ps.setInt(2, member.getInitialPoints());
            ps.setInt(3, member.getStatus());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
	}
	
	/**
	 * Retrieves all members in the database.
	 * @return All members currently in the database.
	 */
	public static ArrayList<Member> getAllMembers() {
		ArrayList<Member> members = new ArrayList<Member>();
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM members");
            ResultSet rs = ps.executeQuery();
            
            // Iterate the result set, adding to the collection.
            while (rs.next()) {
            	Member member = new Member();
                member.setId(rs.getInt("id"));
                member.setName(rs.getString("name"));
                member.setInitialPoints(rs.getInt("initial_points"));
                member.setStatus(rs.getInt("status"));
                members.add(member);
            }
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return members;
	}
	public static void updateMember(Member member) {
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("UPDATE members SET name = ?, initial_points=?, status=? WHERE id = ?");
            ps.setString(1, member.getName());
            ps.setInt(2, member.getInitialPoints());
            ps.setInt(3, member.getStatus());
            ps.setInt(4, member.getId());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }		
	}
	public static void removeMember(Member member) {
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("DELETE FROM members WHERE id = ?");
            ps.setInt(1, member.getId());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }	
	}
	
	public static int getNumberEventsSince(Member member, Date twoWeeksAgo) {
		int returnValue = 0;
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM attendance JOIN event ON attendance.event_id = event.event_id WHERE event.start >= ? AND attendance.member_id = ?");
            ps.setLong(1, twoWeeksAgo.getTime());
            ps.setLong(2, member.getId());
            ResultSet rs = ps.executeQuery();
            
            // Should only get one result, a count.
        	rs.next();            	
            returnValue = rs.getInt(1);
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
		return returnValue;
	}
	
	public String toString() {
		return name;
	}

	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + id;
		return result;
	}

	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		final Member other = (Member) obj;
		if (id != other.id)
			return false;
		return true;
	}
}
